<?php

class Db_scheduler extends CI_Model {
	
	function Db_scheduler()
	{
		parent::__construct();
	}
	
	function getSessions($stream = 0, $track = 0, $page = 1)
	{
		$base = $this->config->item('base_url');
		$showSessions = '';
		$start = ($page * 18) - 18;
		if ($stream == 0 && $track == 0) {
			$total = "SELECT COUNT(*) AS total FROM csessions";
			$mysql = "SELECT * FROM csessions ORDER BY title ASC LIMIT $start, 18";
		} elseif ($stream > 0 && $track == 0) {
			$total = "SELECT COUNT(*) AS total FROM csessions c LEFT JOIN tracks t ON c.tracks_id = t.tracks_id LEFT JOIN streams s ON t.streams_id = s.streams_id WHERE s.streams_id = '$stream'";
			$mysql = "SELECT * FROM csessions c LEFT JOIN tracks t ON c.tracks_id = t.tracks_id LEFT JOIN streams s ON t.streams_id = s.streams_id WHERE s.streams_id = '$stream' ORDER BY title ASC LIMIT $start, 18";
		} elseif ($track > 0) {
			$total = "SELECT COUNT(*) AS total FROM csessions WHERE tracks_id = '$track'";
			$mysql = "SELECT * FROM csessions WHERE tracks_id = '$track' ORDER BY title ASC LIMIT $start, 18";
		}
		$tquery = $this->db->query($total);
		if ($tquery->num_rows() > 0) {
			$row = $tquery->row();
			$count = $row->total;
		}
		$query = $this->db->query($mysql);
		if ($query->num_rows() > 0) {
			$showSessions .= '<ul>';
			foreach ($query->result() as $row)
			{
				$id = $row->csessions_id;
				$title = $row->title;
				$showSessions .= '<li>';
				$showSessions .= '<a href="' . $base . 'scheduler/session/' . $id . '/' . strtolower(preg_replace('/[^A-Za-z0-9]/','_',$title)) . '">' . $title . '</a>';
				$showSessions .= '</li>';
			}
			$showSessions .= '</ul>';
			// get count and set up pagination
			$npages = ceil($count / 18);
			$prevpage = $page - 1;
			$nextpage = $page + 1;
			if ($page == 1 && $npages != 1) {
				$showSessions .= '<div class="page">&nbsp;&nbsp; Page ' . $page . ' of ' . $npages . ' <a href="' . $base . 'scheduler/browse/' . $stream . '/0/' . $nextpage . '">&gt;</a></div>';
			} elseif ($page == $npages && $npages != 1) {
				$showSessions .= '<div class="page"><a href="' . $base . 'scheduler/browse/' . $stream . '/0/' . $prevpage . '">&lt;</a> Page ' . $page . ' of ' . $npages . '</div>';
			} elseif ($npages == 1) {
				$showSessions .= '<div class="page">&nbsp;&nbsp; Page ' . $page . ' of ' . $npages . '</div>';
			} else {
				$showSessions .= '<div class="page"><a href="' . $base . 'scheduler/browse/' . $stream . '/0/' . $prevpage . '">&lt;</a> Page ' . $page . ' of ' . $npages . ' <a href="' . $base . 'scheduler/browse/' . $stream . '/0/' . $nextpage . '">&gt;</a></div>';
			}
			return $showSessions;
		} else {
			return '<p>There are no sessions to display.</p>';
		}
	}
	
	function getBasic($id)
	{
		$base = $this->config->item('base_url');
		$showBasic = '';
		$mysql = "SELECT c.title AS title, c.session_description AS sessiondesc, c.duration AS dur, t.description AS description, r.track AS track, s.stream AS stream
					FROM csessions c
					LEFT JOIN types t ON c.types_id = t.types_id
					LEFT JOIN tracks r ON c.tracks_id = r.tracks_id
					LEFT JOIN streams s ON r.streams_id = s.streams_id
					WHERE csessions_id = '$id'";
		$query = $this->db->query($mysql);
		if ($query->num_rows() > 0) {
			$row = $query->row();
			$title = $row->title;
			$desc = $row->sessiondesc;
			$duration = $row->dur;
			$type = $row->description;
			$track = $row->track;
			$stream = $row->stream;

			$showBasic .= '<h1>' . $title . '</h1>';
			$showBasic .= '<table border=0 cellspacing=5 width=100%><tr>';
			$showBasic .= '<td width=25%><h2>Type</h2>';
			$showBasic .= '<p>' . $type . '</p></td>';
			$showBasic .= '<td width=50%><h2>Stream / Track</h2>';
			$showBasic .= '<p>' . $stream . ' / ' . $track . '</p></td>';
			$showBasic .= '<td width=25%><h2>Duration</h2>';
			$showBasic .= '<p>' . $duration . ' mins</p></td>';
			$showBasic .= '</tr></table>';
			$showBasic .= '<h2>Session Description</h2>';
			$showBasic .= '<p>' . $desc . '</p>';

			return $showBasic;
		} else {
			return '<p>No detailed information could be found for this session. Please try again.</p>';
		}
	}
	
	function getInstances($id)
	{
		$base = $this->config->item('base_url');
		$showAvailable = '';
		$mysql = "SELECT i.instance_id AS session, a.start_id AS start_id, UNIX_TIMESTAMP(a.start_time) AS start, c.duration AS duration, r.room_identifier AS room, v.name AS venue
					FROM csessions_instance i
					LEFT JOIN csessions c ON i.csessions_id = c.csessions_id
					LEFT JOIN all_times a ON i.start_id = a.start_id
					LEFT JOIN rooms r ON i.rooms_id = r.room_id
					LEFT JOIN venues v ON r.venue_id = v.venue_id
					WHERE i.csessions_id = '$id'
					ORDER BY a.start_time ASC";
		$query = $this->db->query($mysql);
		if ($query->num_rows() > 0) {
			$showAvailable .= '<table class="available" border=0 cellspacing=2 width=100%>';
			$showAvailable .= '<tr><th align="left" colspan=3>Available Sessions</th></tr>';
			foreach ($query->result() as $row)
			{
				$iid = $row->session;
				$sid = $row->start_id;
				$start = $row->start;
				$duration = $row->duration;
				$session_date = date('D, F j, Y', $start);
				$session_start = date('g:i a', $start);
				$session_end = date('g:i a', $start+($duration*60));;
				$room = $row->room;
				$venue = $row->venue;
				// get presenters for instance
				$getPresenters = "SELECT a.presenter_name AS name
									FROM csessions_instance i
									LEFT JOIN csessions_presenter p ON i.instance_id = p.instance_id
									LEFT JOIN all_presenters a ON p.presenter_id = a.presenter_id
									WHERE i.instance_id = '$iid' AND i.start_id = '$sid'";
				$query2 = $this->db->query($getPresenters);
				if ($query2->num_rows() > 0) {
					$showPresenters = 'Presented by '; $i = 1;
					foreach ($query2->result() as $row)
					{
						if ($i > 1) {
							$showPresenters .= ', ';
						}
						$showPresenters .= $row->name;
						$i++;
					}
				} else {
					$showPresenters = 'Presenters to be determined.';
				}
				$showAvailable .= '<tr>';
				$showAvailable .= '<td width=20%>' . $session_date . '<br />' . $session_start . ' - ' . $session_end . '</td>';
				$showAvailable .= '<td width=60%>' . $room . ' (' . $venue . ')<br />' . $showPresenters . '</td>';
				$showAvailable .= '<td align="center" width=20%><a class="scheduleSession" href="' . $base . 'scheduler/add/' . $iid . '"><img src="' . $base . 'images/schedule-session.png" height="80%" width="80%" alt="Schedule Session" /></a></td>';
				$showAvailable .= '</tr>';
			}
			$showAvailable .= '</table>';
			return $showAvailable;
		} else {
			return '<p>There are no sessions to display.</p>';
		}
	}
	
	function addSession($iid, $bypass = 0)
	{
		$user = $this->session->userdata('user_id');
		// get the relevant information of the selected session instance to be added:
		$selected = "SELECT i.csessions_id AS session, UNIX_TIMESTAMP(start_time) AS start, MONTH(start_time) AS month, DAY(start_time) AS day, HOUR(start_time) AS hour, MINUTE(start_time) AS minute, c.duration AS duration, r.venue_id AS venue
						FROM csessions_instance i
						LEFT JOIN all_times t ON i.start_id = t.start_id
						LEFT JOIN csessions c ON i.csessions_id = c.csessions_id
						LEFT JOIN rooms r ON i.rooms_id = r. room_id
						WHERE i.instance_id = '$iid'";
		$query = $this->db->query($selected);
		if ($query->num_rows() > 0) {
			$row = $query->row();
			$instance['id'] = $iid;
			$instance['session'] = $row->session;
			$instance['start_time'] = $row->start;
			$instance['month'] = $row->month;
			$instance['day'] = $row->day;
			$instance['start'] = ($row->hour*60) + $row->minute;
			$instance['end'] = $instance['start'] + $row->duration;
			$instance['venue'] = $row->venue;
		}
		
		if ($bypass != 1) {
			$timeConflicts = $this->checkTime($user, $instance);
			$locConflicts = $this->checkLocation($user, $instance);
			$partConflicts = $this->checkParts($user, $instance);
			
			if ($timeConflicts != '' && $locConflicts != '' && $partConflicts != '') {
				return $timeConflicts . $locConflicts . $partConflicts;
			} elseif ($timeConflicts != '' && $locConflicts != '' && $partConflicts == '') {
				return $timeConflicts . $locConflicts;
			} elseif ($timeConflicts != '' && $locConflicts == '' && $partConflicts != '') {
				return $timeConflicts . $partConflicts;
			} elseif ($timeConflicts == '' && $locConflicts !== '' && $partConflicts != '') {
				return $locConflicts . $partConflicts;
			} elseif ($timeConflicts != '' && $locConflicts == '' && $partConflicts == '') {
				return $timeConflicts;
			} elseif ($timeConflicts == '' && $locConflicts != '' && $partConflicts == '') {
				return $locConflicts;
			} elseif ($timeConflicts == '' && $locConflicts == '' && $partConflicts != '') {
				return $partConflicts;
			} else {
				// no conflicts arose
				$mysql = "INSERT INTO oow_user_sessions VALUES (NULL, '$user', '$iid')";
				$query = $this->db->query($mysql);
				return 'no conflicts';
			}
		} else {
			$mysql = "INSERT INTO oow_user_sessions VALUES (NULL, '$user', '$iid')";
			$query = $this->db->query($mysql);
			return 'no conflicts';
		}
	}
	
	function checkTime($user, $instance)
	{
		$base = $this->config->item('base_url');
		$id = $instance['id'];
		$session = $instance['session'];
		$month = $instance['month'];
		$day = $instance['day'];
		$start = $instance['start'];
		$end = $instance['end'];
		$showConflict = ''; $showAlt = '';
		// Limit conflicts to only those items scheduled during the same calendar day
		$existing = "SELECT s.instance_id AS id, MONTH(t.start_time) AS month, DAY(t.start_time) AS day, HOUR(t.start_time) AS hour, MINUTE(t.start_time) AS minute, c.duration AS duration, c.title AS title
					FROM oow_user_sessions s
					LEFT JOIN csessions_instance i ON s.instance_id = i.instance_id
					LEFT JOIN all_times t ON i.start_id = t.start_id
					LEFT JOIN csessions c ON i.csessions_id = c.csessions_id
					WHERE s.user_id = '$user' AND MONTH(t.start_time) = '$month' AND DAY(t.start_time) = '$day'";
		$query = $this->db->query($existing);
		$alt = array();
		if ($query->num_rows() > 0) {
			$i = 0; $j = 0;
			foreach ($query->result() as $row)
			{
				// take each session that is on the same day and check it for conflict
				// if conflict exists, record its instance id
				$Cid = $row->id;
				$Ctitle = $row->title;
				$Cstart = $row->hour*60 + $row->minute;
				$Cend = $Cstart + $row->duration;
				// variables are set, now here we can do the actual comparison
				// there is a conflict if any of the following situations arises
				// if the start time of the new session is after the start time of the compared session AND before the end time of the compared session
				// OR if the end time of the new session is after the start time of the compared session AND before the end time of the compared session
				// OR if the start time and end time of both sessions MATCH
				// OR if the new session starts before and ends after...
				if ( ($start >= $Cstart && $start < $Cend) || ($end > $Cstart && $end <= $Cend) || ($start == $Cstart && $end == $Cend) || ($start < $Cstart && $end > $Cend)) {
					$alt[] = $Cid; // collects any instance id into an array to check for alternatives later
					if ($i == 0) {
						$showConflict .= '<table class="conflict time" border=0 cellspacing=2 width=100%>';
						$showConflict .= '<tr><th align="left">Time-Based Conflicts</th></tr>';
						$j = 1;
					}
					// get reason messaging
					if ($start >= $Cstart && $start < $Cend) {
						$reason = 'The session you are trying to schedule begins during this previously scheduled session.';
					} elseif ($end > $Cstart && $end <= $Cend) {
						$reason = 'The session you are trying to schedule runs over into this session that has previously been scheduled.';
					} elseif ($start == $Cstart && $end == $Cend) {
						$reason = 'The session you are trying to schedule runs at the same start and end time as this previously scheduled session.';
					} elseif ($start < $Cstart && $end > $Cend) {
						$reason = 'The session you are trying to schedule begins before and ends after this previously scheduled session.';
					}
					$showConflict .= '<tr>';
					$showConflict .= '<td><strong>' . $Ctitle . '</strong><p>' . $reason . '</p></td>';
					#$showConflict .= '<td><a style="removeSession" href="' . $base . 'scheduler/remove/' . $Cid . '">Remove Session</a></td>';
					$showConflict .= '</tr>';
					$i++;
				}
			}
			if ($j == 1) {
				$showConflict .= '</table>';
			}
		} else {
			// this is if there are no possible conflicts since there are no items on the same day
		}
		if (!empty($alt)) {
			$gatherAlt = '';
			// first check for alternatives for the session they are currently trying to schedule
			$getAlt = "SELECT i.instance_id AS session, a.start_id AS start_id, UNIX_TIMESTAMP(a.start_time) AS start, c.duration AS duration, c.title AS title, r.room_identifier AS room, v.name AS venue
						FROM csessions_instance i
						LEFT JOIN csessions c ON i.csessions_id = c.csessions_id
						LEFT JOIN all_times a ON i.start_id = a.start_id
						LEFT JOIN rooms r ON i.rooms_id = r.room_id
						LEFT JOIN venues v ON r.venue_id = v.venue_id
						WHERE i.csessions_id = (SELECT csessions_id FROM csessions_instance WHERE instance_id = '$id') AND i.instance_id != '$id' AND i.instance_id NOT IN ( SELECT instance_id FROM oow_user_sessions WHERE user_id = '$user' )";
			$query = $this->db->query($getAlt);
			if ($query->num_rows() > 0) {
				$row = $query->row();
				$session_date = date('D, F j, Y', $row->start);
				$session_start = date('g:i a', $row->start);
				$session_end = date('g:i a', $row->start+($row->duration*60));;
				$gatherAlt .= '<tr><td colspan=2><em>You can try to schedule your desired session at another available time:</em></td></tr>';
				$gatherAlt .= '<tr>';
				$gatherAlt .= '<td width=80%><strong>' . $row->title . '</strong><br />' . $session_date . ' ' . $session_start . ' - ' . $session_end . '<br />' . $row->room . ' (' . $row->venue . ')</td>';
				$gatherAlt .= '<td align="center" width=20%><a class="scheduleSession" href="' . $base . 'scheduler/add/' . $row->session . '"><img src="' . $base . 'images/schedule-session.png" height="80%" width="80%" alt="Schedule Session" /></a></td>';
				$gatherAlt .= '</tr>';
				}
			// then check for alternatives for the existing sessions that are conflicting with the desired session
			$k = 0;
			foreach ($alt as $aid) {
				$getAlt = "SELECT i.instance_id AS session, a.start_id AS start_id, UNIX_TIMESTAMP(a.start_time) AS start, c.duration AS duration, c.title AS title, r.room_identifier AS room, v.name AS venue
							FROM csessions_instance i
							LEFT JOIN csessions c ON i.csessions_id = c.csessions_id
							LEFT JOIN all_times a ON i.start_id = a.start_id
							LEFT JOIN rooms r ON i.rooms_id = r.room_id
							LEFT JOIN venues v ON r.venue_id = v.venue_id
							WHERE i.csessions_id = (SELECT csessions_id FROM csessions_instance WHERE instance_id = '$aid') AND i.instance_id != '$aid' AND i.instance_id NOT IN ( SELECT instance_id FROM oow_user_sessions WHERE user_id = '$user' )";
				$query = $this->db->query($getAlt);
				if ($query->num_rows() > 0) {
					if ($k == 0) {
						$gatherAlt .= '<tr><td colspan=2><em>You can try to reschedule a previously scheduled session to avoid conflicts:</em></td></tr>';	
					}
					$k = 1;
					$row = $query->row();
					$session_date = date('D, F j, Y', $row->start);
					$session_start = date('g:i a', $row->start);
					$session_end = date('g:i a', $row->start+($row->duration*60));;
					$gatherAlt .= '<tr>';
					$gatherAlt .= '<td width=80%><strong>' . $row->title . '</strong><br />' . $session_date . ' ' . $session_start . ' - ' . $session_end . '<br />' . $row->room . ' (' . $row->venue . ')</td>';
					$gatherAlt .= '<td align="center" width=20%><a class="scheduleSession" href="' . $base . 'scheduler/reschedule/' . $row->session . '/' . $aid . '"><img src="' . $base . 'images/reschedule-session.png" height="80%" width="80%" alt="Schedule Session" /></a></td>';
					$gatherAlt .= '</tr>';
				}
			}
			if ($gatherAlt != '') {
				$showAlt .= '<table class="alternative time" border=0 cellspacing=2 width=100%>';
				$showAlt .= '<tr><th colspan=2 align="left">Time-Based Alternatives</th></tr>';
				$showAlt .= $gatherAlt;
				$showAlt .= '</table>';
			}
		}
		return $showConflict . $showAlt;
		
	}
	
	function checkLocation($user, $instance)
	{
		$base = $this->config->item('base_url');
		$id = $instance['id'];
		$session = $instance['session'];
		$month = $instance['month'];
		$day = $instance['day'];
		$start = $instance['start'];
		$end = $instance['end'];
		$venue = $instance['venue'];
		$showConflict = ''; $showAlt = '';
		// Limit conflicts to only those items scheduled during the same calendar day
		$existing = "SELECT s.instance_id AS id, MONTH(t.start_time) AS month, DAY(t.start_time) AS day, HOUR(t.start_time) AS hour, MINUTE(t.start_time) AS minute, c.duration AS duration, c.title AS title, r.venue_id AS venue
					FROM oow_user_sessions s
					LEFT JOIN csessions_instance i ON s.instance_id = i.instance_id
					LEFT JOIN all_times t ON i.start_id = t.start_id
					LEFT JOIN csessions c ON i.csessions_id = c.csessions_id
					LEFT JOIN rooms r ON i.rooms_id = r. room_id
					WHERE s.user_id = '$user' AND MONTH(t.start_time) = '$month' AND DAY(t.start_time) = '$day'";
		$query = $this->db->query($existing);
		$alt = array();
		if ($query->num_rows() > 0) {
			$i = 0; $j = 0;
			foreach ($query->result() as $row)
			{
				// take each session that is on the same day and check it for conflict
				// if conflict exists, record its instance id
				$Cid = $row->id;
				$Ctitle = $row->title;
				$Cstart = $row->hour*60 + $row->minute;
				$Cend = $Cstart + $row->duration;
				$Cvenue = $row->venue;
				$commute = "SELECT MINUTE(commute_time) AS minute FROM commute_times WHERE venue_id1 = '$venue' AND venue_id2 = '$Cvenue'";
				$query2 = $this->db->query($commute);
				if ($query2->num_rows() > 0) {
					$row2 = $query2->row();
					$Ctime = $row2->minute;
				}
				// variables are set, now here we can do the actual comparison
				// there is a conflict if any of the following situations arises
				// if the time between the end of one and the start of the other is less than the commute time, flag it
				// two scenarios exist - $end then $Cstart or $Cend then start
				if ( ($Cstart >= $end && ($Cstart - $end < $Ctime)) || ($start >= $Cend && ($start - $Cend < $Ctime)) ) {
					$alt[] = $Cid; // collects any instance id into an array to check for alternatives later
					if ($i == 0) {
						$showConflict .= '<table class="conflict location" border=0 cellspacing=2 width=100%>';
						$showConflict .= '<tr><th align="left">Location-Based Conflicts</th></tr>';
						$j = 1;
					}
					// get reason messaging
					if ($Cstart >= $end && ($Cstart - $end < $Ctime)) {
						$timeDiff = $Cstart - $end;
						$reason = 'The session you are trying to schedule takes place at a venue which requires ' . $Ctime . ' minutes of travel time to your next scheduled session. However, the amount of time between sessions is ' . $timeDiff . ' minute(s). To attend both, you must miss the end of the first or the beginning of the second.';
					} elseif ($start >= $Cend && ($start - $Cend < $Ctime)) {
						$timeDiff = $start - $Cend;
						$reason = 'The session you are trying to schedule takes place at a venue which requires ' . $Ctime . ' minutes of travel time from your previously scheduled session. However, the amount of time between sessions is ' . $timeDiff . ' minute(s). To attend both, you must miss the end of the first or the beginning of the second.';
					}
					$showConflict .= '<tr>';
					$showConflict .= '<td><strong>' . $Ctitle . '</strong><p>' . $reason . '</p></td>';
					#$showConflict .= '<td><a style="removeSession" href="' . $base . 'scheduler/remove/' . $Cid . '">Remove Session</a></td>';
					$showConflict .= '</tr>';
					$i++;
				}
			}
			if ($j == 1) {
				$showConflict .= '</table>';
			}
		} else {
			// this is if there are no possible conflicts since there are no items on the same day
		}
		if (!empty($alt)) {
			$gatherAlt = '';
			// first check for alternatives for the session they are currently trying to schedule
			$getAlt = "SELECT i.instance_id AS session, a.start_id AS start_id, UNIX_TIMESTAMP(a.start_time) AS start, c.duration AS duration, c.title AS title, r.room_identifier AS room, v.name AS venue
						FROM csessions_instance i
						LEFT JOIN csessions c ON i.csessions_id = c.csessions_id
						LEFT JOIN all_times a ON i.start_id = a.start_id
						LEFT JOIN rooms r ON i.rooms_id = r.room_id
						LEFT JOIN venues v ON r.venue_id = v.venue_id
						WHERE i.csessions_id = (SELECT csessions_id FROM csessions_instance WHERE instance_id = '$id') AND i.instance_id != '$id' AND i.instance_id NOT IN ( SELECT instance_id FROM oow_user_sessions WHERE user_id = '$user' )";
			$query = $this->db->query($getAlt);
			if ($query->num_rows() > 0) {
				$row = $query->row();
				$session_date = date('D, F j, Y', $row->start);
				$session_start = date('g:i a', $row->start);
				$session_end = date('g:i a', $row->start+($row->duration*60));;
				$gatherAlt .= '<tr><td colspan=2><em>You can try to schedule your desired session at another available time:</em></td></tr>';
				$gatherAlt .= '<tr>';
				$gatherAlt .= '<td width=80%><strong>' . $row->title . '</strong><br />' . $session_date . ' ' . $session_start . ' - ' . $session_end . '<br />' . $row->room . ' (' . $row->venue . ')</td>';
				$gatherAlt .= '<td align="center" width=20%><a class="scheduleSession" href="' . $base . 'scheduler/add/' . $row->session . '"><img src="' . $base . 'images/schedule-session.png" height="80%" width="80%" alt="Schedule Session" /></a></td>';
				$gatherAlt .= '</tr>';
				}
			// then check for alternatives for the existing sessions that are conflicting with the desired session
			$k = 0;
			foreach ($alt as $aid) {
				$getAlt = "SELECT i.instance_id AS session, a.start_id AS start_id, UNIX_TIMESTAMP(a.start_time) AS start, c.duration AS duration, c.title AS title, r.room_identifier AS room, v.name AS venue
							FROM csessions_instance i
							LEFT JOIN csessions c ON i.csessions_id = c.csessions_id
							LEFT JOIN all_times a ON i.start_id = a.start_id
							LEFT JOIN rooms r ON i.rooms_id = r.room_id
							LEFT JOIN venues v ON r.venue_id = v.venue_id
							WHERE i.csessions_id = (SELECT csessions_id FROM csessions_instance WHERE instance_id = '$aid') AND i.instance_id != '$aid' AND i.instance_id NOT IN ( SELECT instance_id FROM oow_user_sessions WHERE user_id = '$user' )";
				$query = $this->db->query($getAlt);
				if ($query->num_rows() > 0) {
					if ($k == 0) {
						$gatherAlt .= '<tr><td colspan=2><em>You can try to reschedule a previously scheduled session to avoid conflicts:</em></td></tr>';	
					}
					$k = 1;
					$row = $query->row();
					$session_date = date('D, F j, Y', $row->start);
					$session_start = date('g:i a', $row->start);
					$session_end = date('g:i a', $row->start+($row->duration*60));;
					$gatherAlt .= '<tr>';
					$gatherAlt .= '<td width=80%><strong>' . $row->title . '</strong><br />' . $session_date . ' ' . $session_start . ' - ' . $session_end . '<br />' . $row->room . ' (' . $row->venue . ')</td>';
					$gatherAlt .= '<td align="center" width=20%><a class="scheduleSession" href="' . $base . 'scheduler/reschedule/' . $row->session . '/' . $aid . '"><img src="' . $base . 'images/reschedule-session.png" height="80%" width="80%" alt="Schedule Session" /></a></td>';
					$gatherAlt .= '</tr>';
				}
			}
			if ($gatherAlt != '') {
				$showAlt .= '<table class="alternative location" border=0 cellspacing=2 width=100%>';
				$showAlt .= '<tr><th colspan=2 align="left">Location-Based Alternatives</th></tr>';
				$showAlt .= $gatherAlt;
				$showAlt .= '</table>';
			}
		}
		return $showConflict . $showAlt;
	}
	
	function checkParts($user, $instance)
	{
		// constitutes a parts-based conflict
		// if the user is scheduling part 1 after part 2
		// if the user is scheduling part 2 before part 1
		// if the user is scheduling part 2 and has not scheduled part 1 yet
		$base = $this->config->item('base_url');
		$id = $instance['id'];
		$session = $instance['session'];
		$start = $instance['start_time'];
		$showConflict = ''; $showAlt = '';
		// Limit conflicts to only those items scheduled that are a prereq if there are any prereqs
		// first check will look to see if it HAS prereqs - second will look to see if it IS a prereq
		$hasPrereq = "SELECT p.prereq_id AS prereq_id, c.title AS title
						FROM prereqs p
						LEFT JOIN csessions c ON p.prereq_id = c.csessions_id
						WHERE p.csessions_id = '$session'";
		$query = $this->db->query($hasPrereq);
		$h = 0; $n = 0; $alt = array(); $alt2 = array();
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row)
			{
				// will run once for each PREREQ of the selected session
				$pSession = $row->prereq_id; // gets csession_id of the current prereq
				$pTitle = $row->title; // gets title of the current prereq
				$checkPrereq = "SELECT s.instance_id AS id, UNIX_TIMESTAMP(start_time) AS start
									FROM oow_user_sessions s
									LEFT JOIN csessions_instance i ON s.instance_id = i.instance_id
									LEFT JOIN all_times t ON i.start_id = t.start_id
									WHERE s.user_id = '$user' AND i.csessions_id = '$pSession'";
				$queryHas = $this->db->query($checkPrereq);
				if ($queryHas->num_rows() > 0) {
					$rowHas = $queryHas->row();
					$Cid = $rowHas->id;
					// the prereq is on the schedule - check to make sure it is before
					// if it appears AFTER the session, then it is a CONFLICT
					if ($rowHas->start > $start) {
						// CONFLICT
						$alt[] = $Cid; // collects any instance id into an array to check for alternatives later
						if ($h == 0) {
							$showConflict .= '<table class="conflict location" border=0 cellspacing=2 width=100%>';
							$showConflict .= '<tr><th align="left">Part-Based Conflicts</th></tr>';
							$h = 1;
						}
						$showConflict .= '<tr>';
						$showConflict .= '<td><strong>' . $pTitle . '</strong><p>This session is a prerequisite for the one you are trying to schedule. If you continue scheduling the session above, you will be scheduling it before the time and date you have scheduled it\'s prerequisite session.</p></td>';
						$showConflict .= '</tr>';
					}
				} else {
					// the prereq is not on the schedule at all - CONFLICT
					$n = 1;
					if ($h == 0) {
						$showConflict .= '<table class="conflict location" border=0 cellspacing=2 width=100%>';
						$showConflict .= '<tr><th align="left">Part-Based Conflicts</th></tr>';
						$h = 1;
					}
					$showConflict .= '<tr>';
					$showConflict .= '<td><strong>' . $pTitle . '</strong><p>The session you are trying to schedule has a prerequisite session that you have not yet scheduled.</p></td>';
					$showConflict .= '</tr>';
				}
			}
		}
		
		// second check will check if it IS a Prereq.  If it IS a prereq and it has sessions that follow it scheduled before it, CONFLICT
		$isPrereq = "SELECT p.csessions_id AS id, c.title AS title
						FROM prereqs p
						LEFT JOIN csessions c ON p.csessions_id = c.csessions_id
						WHERE p.prereq_id = '$session'";
		$query = $this->db->query($isPrereq);
		
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row)
			{
				// will run once for each FOLLOW UP SESSION of the selected session
				$fSession = $row->id; // gets csession_id of the current follow up session
				$fTitle = $row->title; // gets title of the current follow up session
				$checkFollowup = "SELECT s.instance_id AS id, UNIX_TIMESTAMP(start_time) AS start
									FROM oow_user_sessions s
									LEFT JOIN csessions_instance i ON s.instance_id = i.instance_id
									LEFT JOIN all_times t ON i.start_id = t.start_id
									WHERE s.user_id = '$user' AND i.csessions_id = '$fSession'";
				$queryIs = $this->db->query($checkFollowup);
				if ($queryIs->num_rows() > 0) {
					$rowIs = $queryIs->row();
					$Cid = $rowIs->id;
					// the follow up is on the schedule - check to make sure it is after
					// if it appears BEFORE the session, then it is a CONFLICT
					if ($rowIs->start < $start) {
						// CONFLICT
						$alt2[] = $Cid; // collects any instance id into an array to check for alternatives later
						if ($h == 0) {
							$showConflict .= '<table class="conflict location" border=0 cellspacing=2 width=100%>';
							$showConflict .= '<tr><th align="left">Part-Based Conflicts</th></tr>';
							$h = 1;
						}
						$showConflict .= '<tr>';
						$showConflict .= '<td><strong>' . $fTitle . '</strong><p>The session you are trying to schedule is a prerequisite to this session, however, if you continue, you will be scheduled to attend the prerequisite session after it\'s follow up session. For best results, you should schedule the prerequisite first.</p></td>';
						$showConflict .= '</tr>';
					}
				} else {
					// in this case, it is not a CONFLICT if the follow up course is not scheduled.
				}
			}
		}
		if ($h == 1) {
			$showConflict .= '</table>';
		}
		if (!empty($alt) || $n == 1) {
			$gatherAlt = '';
			// first check for Part 1's that come before the Part 2
			$getAlt = "SELECT i.instance_id AS session, a.start_id AS start_id, UNIX_TIMESTAMP(a.start_time) AS start, c.duration AS duration, c.title AS title, r.room_identifier AS room, v.name AS venue
						FROM csessions_instance i
						LEFT JOIN csessions c ON i.csessions_id = c.csessions_id
						LEFT JOIN all_times a ON i.start_id = a.start_id
						LEFT JOIN rooms r ON i.rooms_id = r.room_id
						LEFT JOIN venues v ON r.venue_id = v.venue_id
						WHERE i.csessions_id = (SELECT p.prereq_id FROM csessions_instance i LEFT JOIN prereqs p ON p.csessions_id = i.csessions_id WHERE i.instance_id = '$id')
						AND a.start_time < (SELECT start_time FROM csessions_instance i LEFT JOIN all_times t ON t.start_id = i.start_id WHERE i.instance_id = '$id')";
			$query = $this->db->query($getAlt);
			if ($query->num_rows() > 0) {
				$row = $query->row();
				$session_date = date('D, F j, Y', $row->start);
				$session_start = date('g:i a', $row->start);
				$session_end = date('g:i a', $row->start+($row->duration*60));
				if ($n == 1) {
					$gatherAlt .= '<tr><td colspan=2><em>You have not yet scheduled this session\'s prerequisite. You can try to schedule it at this time:</em></td></tr>';
				} else {
					$gatherAlt .= '<tr><td colspan=2><em>You are scheduling a session before the time and date you scheduled it\'s prerequisite. You can try to reschedule the prerequisite:</em></td></tr>';
				}
				$gatherAlt .= '<tr>';
				$gatherAlt .= '<td width=80%><strong>' . $row->title . '</strong><br />' . $session_date . ' ' . $session_start . ' - ' . $session_end . '<br />' . $row->room . ' (' . $row->venue . ')</td>';
				if ($n == 1) {
					$gatherAlt .= '<td align="center" width=20%><a class="scheduleSession" href="' . $base . 'scheduler/add/' . $row->session . '"><img src="' . $base . 'images/schedule-session.png" height="80%" width="80%" alt="Schedule Session" /></a></td>';
				} else {
					$gatherAlt .= '<td align="center" width=20%><a class="scheduleSession" href="' . $base . 'scheduler/reschedule/' . $row->session . '/' . $alt[0] . '"><img src="' . $base . 'images/reschedule-session.png" height="80%" width="80%" alt="Schedule Session" /></a></td>';
				}
				$gatherAlt .= '</tr>';
			}
			if ($gatherAlt != '') {
				$showAlt .= '<table class="alternative part" border=0 cellspacing=2 width=100%>';
				$showAlt .= '<tr><th colspan=2 align="left">Part-Based Alternatives</th></tr>';
				$showAlt .= $gatherAlt;
				$showAlt .= '</table>';
			}
		}
		
		if (!empty($alt2)) {
			$gatherAlt = '';
			// first check for Part 1's that come before the Part 2
			$getAlt = "SELECT i.instance_id AS session, a.start_id AS start_id, UNIX_TIMESTAMP(a.start_time) AS start, c.duration AS duration, c.title AS title, r.room_identifier AS room, v.name AS venue
						FROM csessions_instance i
						LEFT JOIN csessions c ON i.csessions_id = c.csessions_id
						LEFT JOIN all_times a ON i.start_id = a.start_id
						LEFT JOIN rooms r ON i.rooms_id = r.room_id
						LEFT JOIN venues v ON r.venue_id = v.venue_id
						WHERE i.csessions_id = (SELECT p.csessions_id FROM csessions_instance i LEFT JOIN prereqs p ON p.prereq_id = i.csessions_id WHERE i.instance_id = 643)
						AND a.start_time > (SELECT start_time FROM csessions_instance i LEFT JOIN all_times t ON t.start_id = i.start_id WHERE i.instance_id = 643)";
			$query = $this->db->query($getAlt);
			if ($query->num_rows() > 0) {
				$row = $query->row();
				$session_date = date('D, F j, Y', $row->start);
				$session_start = date('g:i a', $row->start);
				$session_end = date('g:i a', $row->start+($row->duration*60));
				$gatherAlt .= '<tr><td colspan=2><em>You may try to reschedule this session\'s follow up so that you are scheduled to attend after it\'s prerequisite:</em></td></tr>';
				$gatherAlt .= '<tr>';
				$gatherAlt .= '<td width=80%><strong>' . $row->title . '</strong><br />' . $session_date . ' ' . $session_start . ' - ' . $session_end . '<br />' . $row->room . ' (' . $row->venue . ')</td>';
				$gatherAlt .= '<td align="center" width=20%><a class="scheduleSession" href="' . $base . 'scheduler/reschedule/' . $row->session . '/' . $alt2[0] . '"><img src="' . $base . 'images/reschedule-session.png" height="80%" width="80%" alt="Schedule Session" /></a></td>';
				$gatherAlt .= '</tr>';
			}
			if ($gatherAlt != '') {
				$showAlt .= '<table class="alternative part" border=0 cellspacing=2 width=100%>';
				$showAlt .= '<tr><th colspan=2 align="left">Part-Based Alternatives</th></tr>';
				$showAlt .= $gatherAlt;
				$showAlt .= '</table>';
			}
		}
		
		return $showConflict . $showAlt;
	}
	
	function getMySchedule()
	{
		$base = $this->config->item('base_url');
		$uid = $this->session->userdata('user_id');
		$showAvailable = '';
		$showMonth = 0;
		$showDay = 0;
		$d = 0;
		$mysql = "SELECT i.instance_id AS session, a.start_id AS start_id, UNIX_TIMESTAMP(a.start_time) AS start, MONTH(a.start_time) AS month, DAY(a.start_time) AS day, c.title AS title, c.duration AS duration, r.room_identifier AS room, v.name AS venue
					FROM oow_user_sessions o
					LEFT JOIN csessions_instance i ON o.instance_id = i.instance_id
					LEFT JOIN csessions c ON i.csessions_id = c.csessions_id
					LEFT JOIN all_times a ON i.start_id = a.start_id
					LEFT JOIN rooms r ON i.rooms_id = r.room_id
					LEFT JOIN venues v ON r.venue_id = v.venue_id
					WHERE o.user_id = '$uid'
					ORDER BY a.start_time ASC";
		$query = $this->db->query($mysql);
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row)
			{
				$iid = $row->session;
				$sid = $row->start_id;
				$start = $row->start;
				$title = $row->title;
				$duration = $row->duration;
				$session_date = date('D, F j, Y', $start);
				$session_start = date('g:i a', $start);
				$session_end = date('g:i a', $start+($duration*60));;
				$room = $row->room;
				$venue = $row->venue;
				$month = $row->month;
				$day = $row->day;
				if ($showMonth != $month || $showDay != $day) {
					if ($d > 0) { $showAvailable .= '</table>'; }
					$showAvailable .= '<table class="available" border=0 cellspacing=2 width=100%>';
					$showAvailable .= '<tr><th align="left" colspan=3>' . $session_date . '</th></tr>';
					$showMonth = $month;
					$showDay = $day;
					$d++;
				}
				
				// get presenters for instance
				$getPresenters = "SELECT a.presenter_name AS name
									FROM csessions_instance i
									LEFT JOIN csessions_presenter p ON i.instance_id = p.instance_id
									LEFT JOIN all_presenters a ON p.presenter_id = a.presenter_id
									WHERE i.instance_id = '$iid' AND i.start_id = '$sid'";
				$query2 = $this->db->query($getPresenters);
				if ($query2->num_rows() > 0) {
					$showPresenters = 'Presented by '; $i = 1;
					foreach ($query2->result() as $row)
					{
						if ($i > 1) {
							$showPresenters .= ', ';
						}
						$showPresenters .= $row->name;
						$i++;
					}
				} else {
					$showPresenters = 'Presenters to be determined.';
				}
				$showAvailable .= '<tr>';
				$showAvailable .= '<td width=20%>' . $session_start . ' - ' . $session_end . '</td>';
				$showAvailable .= '<td width=60%><strong>' . $title . '</strong><br />' . $room . ' (' . $venue . ')<br />' . $showPresenters . '</td>';
				$showAvailable .= '<td align="center" width=20%><a class="removeSession" href="' . $base . 'scheduler/remove/' . $iid . '"><img src="' . $base . 'images/remove-session.png" height="80%" width="80%" alt="Remove Session" /></a></td>';
				$showAvailable .= '</tr>';
			}
			$showAvailable .= '</table>';
			return $showAvailable;
		} else {
			return '<p>There are no sessions to display.</p>';
		}
	}
	
	function removeSession($iid)
	{
		$user = $this->session->userdata('user_id');
		$mysql = "DELETE FROM oow_user_sessions WHERE user_id = '$user' AND instance_id = '$iid'";
		$query = $this->db->query($mysql);
	}
	
	function getStreams()
	{
		$base = $this->config->item('base_url');
		$track = $this->session->userdata('browse_track');
		$page = $this->session->userdata('browse_page');
		$showStreams = '<ul>';
		$mysql = "SELECT streams_id, stream FROM streams ORDER BY stream ASC";
		$query = $this->db->query($mysql);
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row)
			{
				$sid = $row->streams_id;
				$stream = $row->stream;
				$showStreams .= '<li>';
				$showStreams .= '<a href="' . $base . 'scheduler/browse/' . $sid . '/' . $track . '/1">' . $stream . '</a>';
				$showStreams .= '</li>';
			}
		}
		$showStreams .= '</ul>';
		return $showStreams;
	}
	
	function getStreamName($stream)
	{
		$base = $this->config->item('base_url');
		$page = $this->session->userdata('browse_page');
		$mysql = "SELECT stream FROM streams WHERE streams_id = '$stream'";
		$query = $this->db->query($mysql);
		if ($query->num_rows() > 0) {
			$row = $query->row();
			return '<ul><li><a class="removeLayer" href="' . $base . 'scheduler/browse/0/0/' . $page . '"><img src="' . $base . 'images/close.png" alt="Remove"/></a> ' . $row->stream . '</li></ul>';
		}
	}
	
	function getTracks($stream)
	{
		$base = $this->config->item('base_url');
		$page = $this->session->userdata('browse_page');
		$showTracks = '<ul>';
		$mysql = "SELECT tracks_id, track FROM tracks WHERE streams_id = '$stream' ORDER BY track ASC";
		$query = $this->db->query($mysql);
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row)
			{
				$tid = $row->tracks_id;
				$track = $row->track;
				$showTracks .= '<li>';
				$showTracks .= '<a href="' . $base . 'scheduler/browse/' . $stream . '/' . $tid . '/' . $page . '">' . $track . '</a>';
				$showTracks .= '</li>';
			}
		}
		$showTracks .= '</ul>';
		return $showTracks;
	}
	
	function getTrackName($track)
	{
		$base = $this->config->item('base_url');
		$stream = $this->session->userdata('browse_stream');
		$page = $this->session->userdata('browse_page');
		$mysql = "SELECT track FROM tracks WHERE tracks_id = '$track'";
		$query = $this->db->query($mysql);
		if ($query->num_rows() > 0) {
			$row = $query->row();
			return '<ul><li><a class="removeLayer" href="' . $base . 'scheduler/browse/' . $stream . '/0/' . $page . '"><img src="' . $base . 'images/close.png" alt="Remove"/></a> ' . $row->track . '</li></ul>';
		}
	}
	
}

/* End of file db_scheduler.php */
/* Location: roadrunner/models/db_scheduler.php */
?>